/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package brisollogic;

import jBCrypt.BCrypt;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Alex
 */
public class Logic {

    private static final DatabaseConnection DATABASE = DAL.INSTANCE;   

    public Logic() {
        
    }
    
    /**
     * Inserts the passed down Product object into the database
     * @param prod
     * @throws SQLException
     * @throws ClassNotFoundException     
     */
    public void insertProductIntoDb(Product prod) throws SQLException, ClassNotFoundException {
        validateObject(prod, "insertProductIntoDb");
        String sqlQuery = "USE BrisolDB INSERT INTO Product(ModelName, Specs, Category, Picture) VALUES(?,?,?,?)";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {
            pstmt.setString(1, prod.getModelName());
            pstmt.setString(2, prod.getSpecs());
            pstmt.setString(3, prod.getCat().toString());
            pstmt.setString(4, prod.getPictureURL());
            pstmt.executeUpdate();
        }
    }
    
    /**
     * Inserts a User object into the database
     * @param user
     * @throws SQLException
     * @throws ClassNotFoundException 
     */
    public void insertUserIntoDb(User user) throws SQLException, ClassNotFoundException {
        validateObject(user, "insertUserIntoDb");
        String sqlQuery = "USE BrisolDB INSERT INTO Users (UserName, Email, UserStatus, Password) VALUES(?,?,?,?)";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getStatus().toString());
            pstmt.setString(4, user.getPassword());
            pstmt.executeUpdate();
        }
    }
    
    /**
     * Takes all Product objects in the database and return them in a list<Product> 
     * @return List<Product>
     * @throws SQLException
     * @throws ClassNotFoundException 
     */
    public List<Product> showAllProducts() throws SQLException, ClassNotFoundException {
        String sqlQuery = "USE BrisolDB SELECT * FROM Product";
        List<Product> listProd = new ArrayList<>();
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery);
                ResultSet rs = pstmt.executeQuery()
          ) {
            while (rs.next()) {              
                int productID = rs.getInt(1);
                String modelName = rs.getString(2);
                String specs = rs.getString(3);
                String category = rs.getString(4);
                String pictureURL = rs.getString(5);
                Product prod = new Product(productID, modelName, specs, Product.Category.valueOf(category), pictureURL);
                listProd.add(prod);
            }
        }
        return listProd;
    }
    
    /**
     * Takes all User objects from the database and return them in a List<User>
     * @return List<User>
     * @throws SQLException
     * @throws ClassNotFoundException 
     */
    public List<User> showAllUsers() throws SQLException, ClassNotFoundException {
        String sqlQuery = "USE BrisolDB SELECT * FROM Users";
        List<User> listUser = new ArrayList<>();
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery);
                ResultSet rs = pstmt.executeQuery()
          ) {
            while (rs.next()) {
                int userId = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                User.Status userStatus = User.Status.valueOf(rs.getString(4));
                String pass = rs.getString(5);
                User user = new User.UserBuilder(UserCredentials.getInstance(email, pass)).id(userId).name(name).status(userStatus).build();
                listUser.add(user);
            }
        }
        return listUser;
    }

    public Product getProductById(int id) throws SQLException, ClassNotFoundException {
        String sqlQuery = "USE BrisolDB SELECT * FROM Product WHERE ProductId = ?";
        Product prod = null;
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {            
            pstmt.setInt(1, id);
            try (
                    ResultSet rs = pstmt.executeQuery()                    
              ) {
                while (rs.next()) {
                    int productID = rs.getInt(1);
                    String modelName = rs.getString(2);
                    String specs = rs.getString(3);
                    String category = rs.getString(4);
                    String pictureURL = rs.getString(5);
                    prod = new Product(productID, modelName, specs, Product.Category.valueOf(category), pictureURL);
                }            
            }            
        } 
        return prod;
    }

    public User getUserById(int id) throws SQLException, ClassNotFoundException {
        String sqlQuery = "USE BrisolDB SELECT * FROM Users WHERE UserId = ?";
        User user = null;
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {
            pstmt.setInt(1, id);
            try (
                    ResultSet rs = pstmt.executeQuery()
              ) {
                while (rs.next()) {
                    int userId = rs.getInt(1);
                    String userName = rs.getString(2);
                    String mail = rs.getString(3);
                    User.Status userStatus = User.Status.valueOf(rs.getString(4));
                    String password = rs.getString(5);
                    user = new User.UserBuilder(UserCredentials.getInstance(mail, password)).id(userId).name(userName).status(userStatus).build();
                }
            }
        }
        return user;
    }

    /**
     * returns a User object from the database based on the passed down Credentials object
     * Will return a null User object if the passed down Credentials doesn't match the User's 
     * @param Credentials 
     * @return User
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public User getUserByCredential(Credentials cred) throws SQLException, ClassNotFoundException {
        validateObject(cred, "getUserByCredential");
        String sqlQuery = "USE BrisolDB SELECT * FROM Users WHERE Email = ?";
        User user = null;
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)               
          ) {            
            pstmt.setString(1, cred.getEmail());
            try (
                    ResultSet rs = pstmt.executeQuery();                    
              ) {
                while (rs.next()) {
                    int userID = rs.getInt(1);
                    String userName = rs.getString(2);
                    String userEmail = rs.getString(3);
                    User.Status userStatus = User.Status.valueOf(rs.getString(4));
                    String userPassword = rs.getString(5);
                    user = new User.UserBuilder(UserCredentials.getInstance(userEmail, userPassword)).id(userID).name(userName).status(userStatus).build();
                }
            }            
            if (user != null) {
                if(!BCrypt.checkpw(cred.getPassword(), user.getPassword())) {
                    user = null;
                }
            }
        }
        return user;
    }

    public void updateUser(User user) throws SQLException, ClassNotFoundException {
        validateObject(user, "updateUser");
        String sqlQuery = "USE BrisolDB UPDATE Users SET UserName=?,Email=?,UserStatus=?,Password=? WHERE UserId=?";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {            
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getStatus().toString());
            pstmt.setString(4, user.getPassword());
            pstmt.setInt(5, user.getUserId());
            pstmt.executeUpdate();
        }
    }

    public void updateProduct(Product prod) throws SQLException, ClassNotFoundException {
        validateObject(prod, "updateProduct");
        String sqlQuery = "USE BrisolDB UPDATE Product SET ModelName=?,Specs=?,Category=?,Picture=? WHERE ProductId=?";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {            
            pstmt.setString(1, prod.getModelName());
            pstmt.setString(2, prod.getSpecs());
            pstmt.setString(3, prod.getCat().toString());
            pstmt.setString(4, prod.getPictureURL());
            pstmt.setInt(5, prod.getProductid());
            pstmt.executeUpdate();
        }
    }

    public void deleteProduct(Product prod) throws SQLException, ClassNotFoundException {
        validateObject(prod, "deleteProduct");
        String sqlQuery = "USE BrisolDB DELETE FROM Product WHERE ProductId=?";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {            
            pstmt.setInt(1, prod.getProductid());
            pstmt.executeUpdate();
        }
    }

    public void deleteUser(User user) throws SQLException, ClassNotFoundException {
        validateObject(user, "deleteUser");
        String sqlQuery = "USE BrisolDB DELETE FROM Users WHERE UserId=?";
        try (
                Connection con = DATABASE.getConnection();
                PreparedStatement pstmt = con.prepareStatement(sqlQuery)
          ) {        
            pstmt.setInt(1, user.getUserId());
            pstmt.executeUpdate();
        }
    }
    
    private void validateObject(Object o, String methodeName) {
        if(o == null) {
            throw new NullPointerException("Parameter for methode " + methodeName + " must not be null!");
        }
    }
}
